#include "database.h"
#include<QtSql/QtSql>
Database::Database()
{
        QString DeviceID="1234";
        db= QSqlDatabase::addDatabase("QSQLITE");
        db.setDatabaseName("EX0.db3");
        if(db.open()){
            QSqlQuery query(db);
                createTB();
                AddFarm("F1","local");
                QString FarmID=selectFarmIDformFarmname("F1");
                AddDeviceToFarm(DeviceID,FarmID);

                //AddFamily("TestAll",DeviceID);
                //AddMs(DeviceID,"1234","aaa.jpg","134","1231","1321","132","132","132");
                qDebug()<<"tabela ses";

        }else{
            qDebug()<<"DB No aberto";
        }
        db.close();

}

//def __init__(self,data):
//    self.conn = sqlite3.connect(data)
//    self.db = self.conn.cursor()

//    try:
//        global DeviceID,FarmID
//        self.createTB()
//        self.AddFarm("F1","local")
//        FarmID=self.selectFarmIDformFarmname("F1")
//        self.AddDeviceToFarm(DeviceID,FarmID)
//        print 'Tabal have never Create'
//    except sqlite3.DatabaseError,e:
//        print  e
void Database::insertFarm(QString name,QString address){
    QSqlQuery query(db);
    QString qstr ="insert into farm(name,address) values (\""+name+"\",\""+address+"\")";
    qDebug()<<query.exec(qstr)<<" inFarm";
    db.commit();
}

void Database::insertFamily(QString name,QString species,QString typecode,QString farmID){
     QSqlQuery query(db);
     qDebug()<<query.exec("insert into family(Name,Species,TypeCode,FarmID) values (\""+name+"\",\""+species+"\","+typecode+","+farmID+")")<<" insFamily";

     db.commit();
}

void Database::insertFish(QString FamilyID,QString RFID,QString Sex){
     QSqlQuery query(db);
     qDebug()<<query.exec("insert into fish(FamilyID,RFID,Sex) values ("+FamilyID+",\""+RFID+"\",\""+Sex+"\")")<<" insFlish";
     db.commit();
}


void Database::insertBreader(QString FamilyID,QString FishID){
      QSqlQuery query(db);
      qDebug()<<query.exec("insert into breader(FamilyID,FishID) values ("+FamilyID+","+FishID+")");
      db.commit();
}

void Database::insertMeasurementFish(QString DeviceID,QString FishID,QString ImageID,QString Weight,QString StandardLength,QString TotalLength,QString HeadLength,QString BodyWidth,QString BodyDepth){
    QSqlQuery query(db);
    qDebug()<<query.exec("insert into measurementFish(DeviceID,FishID,ImageID,Weight,StandardLength,TotalLength,HeadLength,BodyWidth,BodyDepth) values ("+DeviceID+","+FishID+","+ImageID+","+Weight+","+StandardLength+","+TotalLength+","+HeadLength+","+BodyWidth+","+BodyDepth+")");
    db.commit();
}

void Database::insertImage(QString ImagePath){
     QSqlQuery query(db);
    qDebug()<<query.exec("insert into image(ImagePath) values (\""+ImagePath+"\")");
    db.commit();
}

void Database::insertdevice(QString DeviceID,QString FarmID){
     QSqlQuery query(db);
     qDebug()<<DeviceID<<FarmID;
    qDebug()<<query.exec("insert into device(DeviceID,FarmID) values ("+DeviceID+","+FarmID+")");
    qDebug()<<query.lastError();
    db.commit();
}

//void Database::pritt(self,data):
//    for row in data:
//        print row
void Database::insertFA(QString data){
    QSqlQuery query(db);
    qDebug()<<query.exec("insert into fname(name) values ("+data+")");
    db.commit();
}

void Database::createTB(void){
     QSqlQuery query(db);
     query.exec("CREATE TABLE  farm ( FarmID INTEGER  PRIMARY KEY , name TEXT NOT NULL , address TEXT NULL,Flag INTEGER default(0))");
     query.exec("CREATE TABLE  image ( ImageID INTEGER PRIMARY KEY , ImagePath TEXT NOT NULL ,Flag INTEGER default(0) )");
     query.exec("CREATE TABLE  device ( DeviceID INTEGER   PRIMARY KEY , FarmID INT NOT NULL ,Flag INTEGER default(0) ,FOREIGN KEY (FarmID) REFERENCES Farm(FarmID) ON DELETE CASCADE  ON UPDATE CASCADE) ");
     query.exec("CREATE TABLE  family ( FamilyID INTEGER   PRIMARY KEY, Name TEXT  NOT NULL , Species TEXT NULL , TypeCode INTEGER NULL , FarmID INTEGER NOT NULL ,Flag INTEGER default(0) ,  FOREIGN KEY (FarmID) REFERENCES Farm(FarmID) ON DELETE CASCADE)");
     query.exec("CREATE TABLE  breader ( FishID INT NOT NULL , FamilyID INT NOT NULL ,Flag INTEGER default(0),   FOREIGN KEY (FishID) REFERENCES Fish(FishID) ,	FOREIGN KEY (FamilyID) REFERENCES family(FamilyID),PRIMARY KEY (FamilyID,FishID))");
     query.exec("CREATE TABLE  fish ( FishID INTEGER  PRIMARY KEY, FamilyID INT NOT NULL , RFID TEXT NOT NULL, Sex	TEXT  NULL,Flag INTEGER default(0) ,   FOREIGN KEY (FamilyID) REFERENCES Family(FamilyID)                      ON DELETE CASCADE  ON UPDATE CASCADE )");
     query.exec("CREATE TABLE  measurementFish ( MesID INTEGER  PRIMARY KEY, FishID INT NOT NULL , DeviceID INT NOT NULL, ImageID INT NOT NULL, TimeStamp timestamp default (strftime('%s', 'now')), Weight FLOAT   NULL, StandardLength	FLOAT  NULL, TotalLength FLOAT   NULL, HeadLength FLOAT   NULL, BodyWidth	FLOAT  NULL, BodyDepth	FLOAT  NULL,Flag INTEGER default(0) , FOREIGN KEY (DeviceID) REFERENCES Device(DeviceID)                      ON DELETE CASCADE  ON UPDATE CASCADE, FOREIGN KEY (FishID) REFERENCES Fish(FishID)                      ON DELETE CASCADE  ON UPDATE CASCADE,FOREIGN KEY (ImageID) REFERENCES Image(ImageID)                      ON DELETE CASCADE  ON UPDATE CASCADE ) ");
     query.exec("create table fname (ID INTEGER PRIMARY KEY,name TEXT UNIQUE NOT NULL,Flag INTEGER default(0))");
     query.exec("CREATE TABLE  pond ( PondID INTEGER  PRIMARY KEY , name TEXT NOT NULL , FarmID TEXT NULL,Flag INTEGER default(0),FOREIGN KEY (FarmID) REFERENCES Farm(FarmID)) ");
     db.commit();
}
//////////////////////Select

QString Database::selectImageIDFormImagePath(QString ImagePath) {
     QSqlQuery query(db);
     QString re;
     query.exec("select ImageID from Image where ImagePath = "+ImagePath);
     while (query.next()){
         re=query.value(0).toString();
     }
     return re;
}
void Database::selectMas(void){
    QSqlQuery query(db);
    query.exec("select * from measurementFish");

    while (query.next()){
        qDebug()<<query.size()<<"aaasdsad";
        qDebug()<<query.value(0).toString();
    }
}
void Database::selectNamefamily(void){
     QSqlQuery query(db);
     query.exec("select Name from family");
    while (query.next()){
        qDebug()<<query.value(0).toString();
    }
}
void Database::selectNamePond(void){
    QSqlQuery query(db);
    query.exec("select name from pond");
    while (query.next()){
        qDebug()<<query.value(0).toString();
    }
}

QString  Database::selectFishIDFormRFID(QString RFID){
    QSqlQuery query(db);
    QString re;
    query.exec("select FishID from fish where RFID = "+RFID);
    while (query.next()){
        re=query.value(0).toString();
    }
    return re;
}
QString Database::selectFamilyIDFormFamilyname(QString Name){
     QSqlQuery query(db);
     QString re;
     query.exec("select FamilyID from family where Name = "+Name);
    while (query.next()){
                re=query.value(0).toString();
    }
    return re;
}
QString Database::selectFarmIDformDeviceID(QString DeviceID){
      QSqlQuery query(db);
      QString re;
      query.exec("select FarmID from device where DeviceID = "+DeviceID );
      while (query.next()){
                  re= query.value(0).toString();
      }
      return re;
}
QString  Database::
selectFarmIDformFarmname(QString Farmname){
    QSqlQuery query(db);
    QString re;
    query.exec("select FarmID from farm where name = "+Farmname);
    while (query.next()){
           re=query.value(0).toString();
           qDebug()<<re;
    }
    return re;
}
QString Database::selectFamily(void){
    QSqlQuery query(db);
     QString re;
    query.exec("select * from family");
    while (query.next()){
        qDebug()<<"Family";
        re=query.value(0).toString();
        return re;
    }
}
QString Database::selectcountFamily(void){
    QSqlQuery query(db);
    query.exec("select count(*) from family");
//    data=row.fetchone()[0]
//    return str(data)
    QString print;
    while (query.next()){
       print=query.value(0).toString();
       return print;
    }
}
void Database::close(void){
    db.close();

}
//////////////////////ADD
void Database::AddFamily(QString name,QString Deviceid){
//    #ADDFamily
    QString farmid =selectFarmIDformDeviceID(Deviceid);
    insertFamily(name,"fish","123455",farmid);
}
void Database::AddBreader(QString Familyname,QString RFID){
//    #addBreader
    QString familyid=selectFamilyIDFormFamilyname(Familyname);
    QString fishID=selectFishIDFormRFID(RFID);
    insertBreader(familyid,fishID);
}
void Database::AddBreading(QString Familyname,QString RFID,QString sex){
//    #addBreading
    QString familyid=selectFamilyIDFormFamilyname(Familyname);
    insertFish(familyid,RFID,sex);
}
void Database::AddFarm(QString Farmname,QString FarmAdd){
//    #AddFarm
    insertFarm(Farmname,FarmAdd);
}
void Database::AddDeviceToFarm(QString DeviceID,QString FarmID){
    insertdevice(DeviceID,FarmID);
}
void Database::AddMs(QString DeviceID,QString RFID,QString ImagePath,QString Weight, QString StandardLength,QString TotalLength,QString HeadLength,QString BodyWidth,QString BodyDepth){
//    #MS
    QString fishID=selectFishIDFormRFID(RFID);
    insertImage(ImagePath);
    QString imageID=selectImageIDFormImagePath(ImagePath);
    insertMeasurementFish(DeviceID,fishID,imageID,Weight,StandardLength,TotalLength,HeadLength,BodyWidth,BodyDepth);
}

